package com.mipo.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mipo.dao.BookTypeDao;
import com.mipo.util.DBUtils;
import com.mipo.util.PageTools;
import com.mipo.util.SysConfigUtils;
import com.mipo.vo.Book;
import com.mipo.vo.BookType;
import com.mipo.vo.OrderItem;
import com.mipo.vo.Orders;

public class BookTypeDaoImp implements BookTypeDao {

	public List<BookType> list() throws Exception {
		List<BookType> list = new ArrayList<BookType>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from bookType");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			BookType bookType = new BookType(rs.getInt(1), rs.getString(2), rs
					.getString(3));
			list.add(bookType);
		}
		DBUtils.close(conn);
		System.out.println("list_sql:  " + "select * from bookType");
		return list;
	}

	public List<Book> listZCX() throws Exception {
		List<Book> list = new ArrayList<Book>();
		StringBuffer sql = new StringBuffer();
		sql
				.append("select top 6 sum(o.counts) tj,b.bookid ,b.bookName from orderitem o,book b "
						+ "where o.bookId = b.bookid group by b.bookid,b.bookName order by tj desc");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Book book = new Book();
			book.setCount(rs.getInt(1));
			book.setBookId(rs.getInt(2));
			book.setBookName(rs.getString(3));
			list.add(book);
		}
		DBUtils.close(conn);
		System.out
				.println("listZCX_sql:  "
						+ "select top 6 sum(o.counts) tj,b.bookid ,b.bookName from orderitem o,book b "
						+ "where o.bookId = b.bookid group by b.bookid,b.bookName order by tj desc");
		return list;
	}

	public List<Orders> listOrders(int accid, int currentPage, int everyPageRows)
			throws Exception {
		List<Orders> list = new ArrayList<Orders>();
		StringBuffer sql = new StringBuffer();
		// sql
		// .append("select * from orders where 1=1 and accountid = ? order by
		// orderid");
		sql
				.append(" select top " + everyPageRows
						+ " * from orders where 1=1 ");
		if (accid != -100) {
			sql.append(" and accountid = ? ");
		}

		sql.append(" and orderid not in ( ");
		sql.append(" select top " + (currentPage - 1) * everyPageRows
				+ " o2.orderid from orders o2 where 1=1 ");
		if (accid != -100) {
			sql.append(" and accountid = ? ");
		}
		sql.append(" order by orderid desc) ");
		sql.append(" order by orderid desc");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		if (accid != -100) {
			pst.setInt(1, accid);
			pst.setInt(2, accid);
		}
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Orders o = new Orders();
			o.setOrderId(rs.getString(1));
			o.setAccountId(rs.getInt(2));
			o.setConsigneeName(rs.getString(3));
			o.setConsigneePhon(rs.getString(4));
			o.setConsigneeAddress(rs.getString(5));
			o.setPost(rs.getString(6));
			o.setBale(SysConfigUtils.getBale(rs.getString(7)));
			o.setPay(SysConfigUtils.getPay(rs.getString(8)));
			o.setStatesS(SysConfigUtils.getStates(rs.getInt(9)));
			o.setStates(rs.getInt(9));
			list.add(o);
		}
		DBUtils.close(conn);
		return list;
	}

	public int queryOrdersCount(PageTools pt, int accid) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "select count(*) from orders where 1=1 ";
		if (accid != -100) {
			sql += "and accountId = ? ";
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		if (accid != -100) {
			ps.setInt(1, accid);
		}
		ResultSet rs = ps.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		DBUtils.close(conn);
		return count;
	}

	public List<OrderItem> listOrderItems(String orderId) throws Exception {
		List<OrderItem> list = new ArrayList<OrderItem>();
		StringBuffer sql = new StringBuffer();
		sql
				.append(" select b.*,oi.*,bt.* from orderitem oi,book b,booktype bt ");
		sql.append(" where oi.bookid = b.bookid ");
		sql.append(" and b.typeid = bt.typeid ");
		sql.append(" and oi.orderid = ? ");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		pst.setString(1, orderId);
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Book bo = new Book(rs.getInt(1), rs.getInt(2), rs.getString(3), rs
					.getString(4), rs.getString(5), rs.getString(6), rs
					.getString(7), rs.getString(8), 1, rs.getFloat(9), rs
					.getFloat(10), rs.getString(17));
			OrderItem oi = new OrderItem();
			oi.setOrderItemId(rs.getInt(11));
			oi.setOrderId(rs.getString(12));
			oi.setBookId(rs.getInt(13));
			oi.setCounts(rs.getInt(14));
			oi.setPrice(rs.getFloat(15));
			oi.setBook(bo);
			list.add(oi);
		}
		DBUtils.close(conn);
		return list;
	}
}
